﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using NET;
using MySql.Data.MySqlClient;

public class DepartmentBL
{

    public List<string> ObtenerDepartmentos()
    {
        List<string> departments = new List<string>();
        string query = "SELECT nombre FROM departamentos";
        MySqlConnection myConnection = new MySqlConnection();
        myConnection.ConnectionString = "Persist Security Info=False;Username=root;Password=;database=helpdeskdb;server=localhost;Connect Timeout=30";
        myConnection.Open();

        //create command and assign the query and connection from the constructor
        MySqlCommand cmd = new MySqlCommand(query, myConnection);
        //Execute command
        cmd.ExecuteNonQuery();
        //close connection
        MySqlDataReader dataReader = cmd.ExecuteReader();
        //Read the data and store them in the list
        while (dataReader.Read())
        {

            departments.Add((string)dataReader["nombre"]);

        }

        //close Data Reader
        dataReader.Close();
        //close Connection
        myConnection.Close();



        return departments;
    }

    public long ObtenerJefeDeDepartmento(string departamento)
    {
        long usuario = 0;
        string query1 = "SELECT jefe_id FROM departamentos WHERE nombre = '"+departamento+"'";
        //string query = "SELECT nombre FROM usuarios WHERE usuarios_id = ("+query1+")";
        MySqlConnection myConnection = new MySqlConnection();
        myConnection.ConnectionString = "Persist Security Info=False;Username=root;Password=;database=helpdeskdb;server=localhost;Connect Timeout=30";
        myConnection.Open();

        //create command and assign the query and connection from the constructor
        MySqlCommand cmd = new MySqlCommand(query1, myConnection);
        //Execute command
        cmd.ExecuteNonQuery();
        //close connection
        MySqlDataReader dataReader = cmd.ExecuteReader();
        //Read the data and store them in the list
        while (dataReader.Read())
        {

            usuario = dataReader.GetInt32(0);

        }

        //close Data Reader
        dataReader.Close();
        //close Connection
        myConnection.Close();



        return usuario;
    }


    public void GuardarDepartmento(Department department)
    {

        try
        {

            string query;

            if (department.DepartmentId == 0)
            {
                query = "INSERT INTO `departamentos` (nombre) VALUES ('" + department.Name +")";
            }
            else
            {
                query = "UPDATE departamentos SET nombre = '" + department.Name +" WHERE department_id = " + department.DepartmentId;
            }

            MySqlConnection myConnection = new MySqlConnection();
            myConnection.ConnectionString = "Persist Security Info=False;Username=root;Password=;database=helpdeskdb;server=localhost;Connect Timeout=30";
            myConnection.Open();

            //create command and assign the query and connection from the constructor
            MySqlCommand cmd = new MySqlCommand(query, myConnection);
            //Execute command
            cmd.ExecuteNonQuery();
            //close connection
            myConnection.Close();

        }
        catch (Exception ex)
        {

            //    _Logger.Error("Error in DeleteCourse()", ex);

            throw ex;
        }

    }


    public void DeleteDepartment(string departmentId)
    {
        try
        {
            // RequireRole(sessionUUID, Role.WRITE_ACCESS, Role.USERS);
            string query = "DELETE FROM `departamentos` WHERE departamentos_id = " + departmentId;
            MySqlConnection myConnection = new MySqlConnection();
            myConnection.ConnectionString = "Persist Security Info=False;Username=root;Password=;database=helpdeskdb;server=localhost;Connect Timeout=30";
            myConnection.Open();

            //create command and assign the query and connection from the constructor
            MySqlCommand cmd = new MySqlCommand(query, myConnection);
            //Execute command
            cmd.ExecuteNonQuery();
            //close connection
            myConnection.Close();
        }
        catch (Exception ex)
        {

            //    _Logger.Error("Error in DeleteCourse()", ex);

            throw ex;
        }
    }
}